SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

create procedure dbo.DnnProduct_GetSiteLog2

@PortalID int,
@PortalAlias nvarchar(50),
@StartDate datetime,
@EndDate datetime

as

select dbo.DnnProduct_SiteLog.DateTime,
 'Name' = 
 case
when dbo.DnnProduct_SiteLog.UserId is null then null
else dbo.DnnProduct_Users.FirstName + ' ' + DnnProduct_Users.LastName
end,
 'Referrer' = 
 case 
 when dbo.DnnProduct_SiteLog.Referrer like '%' + @PortalAlias + '%' then null 
 else dbo.DnnProduct_SiteLog.Referrer
 end,
 'UserAgent' = 
 case 
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 1%' then 'Internet Explorer 1'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 2%' then 'Internet Explorer 2'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 3%' then 'Internet Explorer 3'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 4%' then 'Internet Explorer 4'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 5%' then 'Internet Explorer 5'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE 6%' then 'Internet Explorer 6'
 when dbo.DnnProduct_SiteLog.UserAgent like '%MSIE%' then 'Internet Explorer'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/1%' then 'Netscape Navigator 1'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/2%' then 'Netscape Navigator 2'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/3%' then 'Netscape Navigator 3'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/4%' then 'Netscape Navigator 4'
 when dbo.DnnProduct_SiteLog.UserAgent like '%Mozilla/5%' then 'Netscape Navigator 6+'
 else dbo.DnnProduct_SiteLog.UserAgent
 end,
 dbo.DnnProduct_SiteLog.UserHostAddress,
 dbo.DnnProduct_Tabs.TabName
from dbo.DnnProduct_SiteLog
left outer join dbo.DnnProduct_Users on dbo.DnnProduct_SiteLog.UserId = dbo.DnnProduct_Users.UserId 
left outer join dbo.DnnProduct_Tabs on dbo.DnnProduct_SiteLog.TabId = dbo.DnnProduct_Tabs.TabId 
where dbo.DnnProduct_SiteLog.PortalId = @PortalID
and dbo.DnnProduct_SiteLog.DateTime between @StartDate and @EndDate
order by dbo.DnnProduct_SiteLog.DateTime desc









GO
